# -*- coding:utf-8 -*-
#!/usr/bin/python3

# -*- coding: UTF-8 -*-
import time
import smtplib
from email.mime.text import MIMEText
from email.header import Header


import time
import datetime
import mysql.connector

# 创建链接数据库
config1 = {'host': '192.168.1.101',  # 默认127.0.0.1
          'user': 'root', 'password': 'Ccii@562>c', 'port': 3306,  # 默认即为3306
          'database': 'ccii_redesign', 'charset': 'utf8'  # 默认即为utf8
          }

config2 = {'host': 'rm-2zefd6473rz062234ro.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'manager_lisha', 'password': '(_X(8QN42FU4!YG_et32', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }


config = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }

# 查询行情通，买卖通会员数据
def findMemberUserId():
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select user_id from shop_company where  member_grade_id > 3 order  by id desc '
        cursor.execute(stmt, params=())
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询用户信息
'''
def findUserInfo(userId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT
                        su.id userId,
                        store.id storeId,
                        su.company_id companyId,
                        sc.com_name companyName,
                        smg.member_name memberName
                    
                    FROM
                        shop_user su
                    LEFT JOIN shop_store store ON su.id = store.business_id
                    LEFT JOIN shop_company sc ON su.company_id = sc.id
                    LEFT JOIN shop_member_grade smg on sc.member_grade_id = smg.id
                    WHERE su.id = %s
        """

        cursor.execute(stmt, params=(userId, ))
        data = cursor.fetchall()
        # [(52, 101, '7990', '北京养颐国际贸易有限公司', '旗舰会员')]
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 查询数据库列表
def findUserList(date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select id, mobile, created_date from shop_user where  DATE_FORMAT(created_date,"%Y-%m-%d") = %s order by id desc '
        cursor.execute(stmt, params=(date, ))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data

'''
查询店铺访客数
'''
def findStoreUserCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'SELECT COUNT(*), days FROM data_user_active_detail WHERE DATE_FORMAT(create_date,"%Y-%m-%d") = %s GROUP BY days ORDER BY days'
        stmt = """
                        SELECT
                    count(*) storeUserCount
                FROM
                    (
                        SELECT
                            id,
                            create_date,
                            ip_address,
                            referer_url,
                            user_agent,
                            user_id,
                            data_id,
                            token_id,
                            stay_second,
                            type,
                            app_version,
                            c_id
                        FROM
                            data_visit_log
                        WHERE
                            type = 11
                        AND data_id = %s
                        AND DATE_FORMAT(create_date, '%Y-%m-%d') = %s
                        GROUP BY
                            user_id
                    ) a 

        
        """
        cursor.execute(stmt, params=(storeId, date.strftime('%Y-%m-%d'),))
        data = cursor.fetchall()
        # print(data)
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
店铺浏览量-storeVisitCount = storeVisitCount + productVisitCount
'''
def findStoreVisitCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                SELECT
                    count(*) storeVisitCount
                FROM
                    data_visit_log
                WHERE
                    type = 11
                AND data_id = %s
                AND DATE_FORMAT(create_date, '%Y-%m-%d') = %s 
                
                """
        cursor.execute(stmt, params=(storeId, date.strftime('%Y-%m-%d'),))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
商品浏览量-productVisitCount
'''
def findProductVisitCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
               	SELECT
                    COUNT(1) productVisitCount
                FROM
                    data_visit_log
                WHERE
                    DATE_FORMAT(create_date, '%Y-%m-%d') = %s
                AND type = 10
                AND data_id IN (
                    SELECT id FROM shop_product WHERE store_id = %s
                )

                """
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), storeId,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
商品访客数-productUserCount
'''
def findProductUserCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT sum(countValue) productUserCount FROM
                    (
                    SELECT
                        count(*) countValue
                    FROM
                        data_visit_log
                    WHERE
                        DATE_FORMAT(create_date, '%Y-%m-%d') = %s
                    AND type = 10
                    AND data_id IN (
                        SELECT id FROM shop_product WHERE store_id = %s
                        ) 
                    
                    GROUP BY user_id ) a

                """
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), storeId,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
被访问商品数  productViewedCount
'''
def findProductViewedCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT count(countValue) productViewedCount FROM
                    (
                    SELECT
                        count(*) countValue
                    FROM
                        data_visit_log
                    WHERE
                        DATE_FORMAT(create_date, '%Y-%m-%d') = %s
                    AND type = 10
                    AND data_id IN
                    (
                    SELECT id FROM shop_product WHERE store_id = %s
                    ) 
                    
                    GROUP BY data_id ) a
                """
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), storeId,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
商品收藏数  productFavoriteCount
'''
def findProductFavoriteCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT
                        count(*)
                    FROM
                        shop_product_favorite
                    WHERE
                        date_format(created_date, '%Y-%m-%d') = %s
                    AND product_id IN (
                        SELECT
                            id
                        FROM
                            shop_product
                        WHERE
                            store_id = %s
                    )
                """
        cursor.execute(stmt, params=(date.strftime('%Y-%m-%d'), storeId,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
店铺收藏数  storeFavoriteCount
'''
def findStoreFavoriteCount(storeId, date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                   SELECT 
                        count(*) storeFavoriteCount
                    FROM
                        shop_store_favorite
                    WHERE
                        store_id = %s
                    AND date_format(created_date, '%y-%m-%d') = %s
                """
        cursor.execute(stmt, params=(storeId, date.strftime('%Y-%m-%d'),))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
需求访问次数  demandCount
'''
def findDemandCount(userId):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = """
                    SELECT
                        sum(hit_times) demandCount
                    FROM
                        shop_supply_demand
                    WHERE
                        user_id = %s
                """
        cursor.execute(stmt, params=(userId,))
        data = cursor.fetchall()  # print(data)  # cnn.commit()  # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data



'''
保存邮件数据内容到数据库
'''

def saveEmailContent(content = ''):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        item = []
        item.append(content)
        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'insert into data_email_report(content,create_date, type, del_flag) values (%s, NOW(), 5, 0)'
        cursor.execute(stmt, item)
        cnn.commit()
        print("插入数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接



# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset)\
        # .strftime('%Y-%m-%d')
    # print(re_date)
    return re_date


def getMemberData(userId):
    # [(52, 101, '7990', '北京养颐国际贸易有限公司', '旗舰会员')]
    user = findUserInfo(userId)[0]
    print(user)
    yesterday = getdate(1)
    # print('aaaaaaaaaaaaaaaaaa')
    print(yesterday)
    countDataArr = []
    # 店铺访客
    storeUserCount = findStoreUserCount(user[1], yesterday)[0][0]
    countDataArr.append(storeUserCount)

    productVisitCount = findProductVisitCount(user[1], yesterday)[0][0]

    # 店铺浏览量
    storeVisitCount = findStoreVisitCount(user[1], yesterday)[0][0]
    countDataArr.append(storeVisitCount + productVisitCount)

    # 商品访客数
    productUserCount = findProductUserCount(user[1], yesterday)[0][0]
    countDataArr.append(productUserCount)

    # 商品浏览量
    countDataArr.append(productVisitCount)

    # 被访问商品数
    productViewedCount = findProductViewedCount(user[1], yesterday)[0][0]
    countDataArr.append(productViewedCount)

    # 商品收藏数
    productFavoriteCount = findProductFavoriteCount(user[1], yesterday)[0][0]
    countDataArr.append(productFavoriteCount)

    # 店铺收藏书
    storeFavoriteCount = findStoreFavoriteCount(user[1], yesterday)[0][0]
    countDataArr.append(storeFavoriteCount)

    # 需求收藏数
    demandCount = findDemandCount(user[0])[0][0]
    countDataArr.append(demandCount)
    print(countDataArr)

    return countDataArr




'''
获取注册用户数数量html
'''
def getMemberHtml(userId):

    # 只获取昨天
    date_title = ['店铺访客', '店铺浏览', '商品访客', '商品浏览', '被访商品', '商品收藏', '店铺收藏', '需求浏览']
    user = findUserInfo(userId)[0]

    data_arr = getMemberData(userId)
    print(data_arr)    

    content = ''
    if not user[3] :
        companyName = '未完善公司名-'
    else:
        companyName = user[3]
    content += "<br/>"+ companyName + "-" + user[4] +":<br/>"
    content += "数据日期："+ getdate(1).strftime('%Y-%m-%d')
    content += "<table border='2' cellspacing='0'  width='700px'><tr><td style='text-align:center'>名称</td>"

    for title in date_title:
        content += "<td style='text-align:center' >" + title  + "</td>"
        # content += "<td style='text-align:center' >" + date.strftime('%-m.%-d')  + "</td>"

    content+="</tr><tr><td style='text-align:center'  >数量</td>"

    for item in data_arr:
        content += "<td style='text-align:center' >" + str(item) + "</td>"

    content += "</tr></table>"
    content += "<br/>"

    # if len(data_yesterday) > 0:
    #     print(data_yesterday)
    print("查询数据处理完成！")
    return content


def send_email(content):
    # 第三方 SMTP 服务
    mail_host = "smtp.exmail.qq.com"  # 设置服务器
    mail_user = "coldeal@cciigroup.com"  # 用户名
    mail_pass = "intlCCII2020"  # 口令

    sender = 'coldeal@cciigroup.com'
    receivers = [
                 'zhaon@cciigroup.com', 'dengpr@cciigroup.com', 'liupy@cciigroup.com', 'liuyn@cciigroup.com',
                 'liyf@cciigroup.com', 'lis@cciigroup.com'
                ]
    
    # receivers = ['lis@cciigroup.com']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
    # 邮件正文内容
    message = MIMEText(content, 'HTML', 'utf-8')
    # 发件人名称
    message['From'] = Header("coldeal@cciigroup.com", 'utf-8')

    # 收件人名称
    # message['To'] = Header("每日会员注册量", 'utf-8')

    # 邮件名称 Python SMTP 邮件测试
    subject = '每天付费会员数据统计'
    message['Subject'] = Header(subject, 'utf-8')
    print("开始发送")
    # try:
    smtpObj = smtplib.SMTP_SSL(mail_host)
    #smtpObj.connect(mail_host, 587)  # 25 为 SMTP 端口号
    #smtpObj.starttls()
    smtpObj.login(mail_user, mail_pass)
    for receiver in receivers:
        message['To'] = Header(receiver, 'utf-8')
        smtpObj.sendmail(sender, receiver, message.as_string())

    print("邮件发送成功")

'''
主调用方法
'''
def work():
    # userIds =[39648, 42936, 42822, 52]
    userIds = []
    ids = findMemberUserId()

    for item in ids:
        userIds.append(item[0])

    if len(userIds) == 0 :
        print("没有付费会员数据")
        return


    todayDate = getdate(0)
    content = "<span style='font-size:16px;color:#e92e2e;'>数据生成日期：" + todayDate.strftime('%Y-%m-%d') + "</span><br/>"

    for userId in userIds:
        content += getMemberHtml(userId)

    content += ""
    content += "<span style='font-size:16px;color:#e92e2e;'>备注：</span>需求浏览为截止当天的总数量,其它仅为数据日期当天的统计数据<br/>"
    print(content)
    send_email(content)

    try:
        # print(1)
        saveEmailContent(content)
    except mysql.connector.Error as e:
        print('保存邮件数据异常', str(e))

if __name__ == "__main__":
    work()
